Database Best Backup Strategy

1. Intro

Best backup strategy is done in 3 steps: "Full Database Backup", "Differential Database Backup" and "Transaction Log Backup".

Restoring - The process contains these steps:

Best backup strategy is Full Database Backups, Differential Database Backups AND Transaction Log Backups.

Restoring - The process contains these steps:

Data Copy Phase - Copying data from a backup

Redo Phase (Roll Forward) - Applying logged transactions to the data (Redo/Roll Forward Phase).

The Recovery Point - The goal of roll forward is to return the data to its original state at the recovery point. The recovery point is the point to which the user specifies that the set of data be recovered. Under the full or bulk-logged recovery model, you can specify the recovery point as a particular point in time, a marked transaction, or a log sequence number.

Undo Phase (Roll Back) - the last phase of a restore sequence. The undo phase ends a restore sequence by rolling back any uncommitted transactions. Undo completes the restore sequence, and subsequent backups cannot be restored in that sequence. At the end of the undo phase, the database is brought online and becomes available to users. The undo phase occurs only if necessary to make the data consistent during recovery. When redo completes rolling forward the log transactions, the database might contain changes made by transactions that are uncommitted at the recovery point.

RESTORE command options:

WITH STOPAT - option to set recovery point. (Example.: ...WITH STOPAT = 'Apr 15, 2005 12:00 AM')

NORECOVERY - Indicates that roll back should be delayed after roll forward is completed for the current backup. This allows roll forward to continue with the next RESTORE statement in the sequence.

RECOVERY - Indicates that roll back should be performed after roll forward is completed for the current backup. This brings database online.

BACKUP command options:

DATABASE, FULL - Backs up the entire database including the transaction log.

DATABASE, DIFFERENTIAL - Backs up database including the transaction log from the last successfully executed DATABASE FULL backup.

LOG - The log is backed up from the last successfully executed LOG backup to the current end of the log.

Note: If you recover a database to a previous point in time and begin using the database from that point, you cannot apply any pre-existing log backups taken after the time of the recovery fork point.

You can look into Tools_Maintenance_CreateBackup.sql and Tools_Maintenance_RestoreBackup.sql procedure for ideal backup example. This script is 'E=mc²' of SQL backup.

Use these extensions for backup:

.bak - Full Database Backup
.dif - Differential Backup
.trn - Transaction Log Backup
.fil - Filegroup Backup

2. Backup Sequencence

Backup database first. SQL Server 2005 requires that you back up the tail of the log before restoring a database that is currently attached on the server instance..

1. Create regular Full Database Backup (every weekend).

-- Create a full database backup. INIT clause to overwrite the backup media.
-- During a full database or differential backup, SQL Server backs up enough
-- of the transaction log to produce a consistent database when the database is restored.
BACKUP DATABASE databaseName
    TO DISK = 'C:\BACKUP\SQL\DatabaseName_20081230_000000_full.bak'
    WITH INIT, STATS = 10

2. Create a Differential Database backup periodically between Full Database Backups (every night).

-- Create a differential database backup, appending the backup
-- to the backup device containing the database backup.
BACKUP DATABASE databaseName
    TO DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_diff.dif'
    WITH INIT, DIFFERENTIAL, STATS = 10

3. If using Full or Bulk-Logged Recovery, create Transaction Log Backups (every hour).

-- Create a transaction log backup.
-- INIT - Create new or overwrite existing backup file.
BACKUP LOG databaseName
    TO DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH INIT-- Create a transaction log backup.
-- NOINIT - Append backup to current file.
BACKUP LOG databaseName
    TO DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH NOINIT-- Create a transaction log backup.
-- NOINIT - Append backup to current file.
BACKUP LOG databaseName
    TO DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH NOINIT

Note: BACKUP LOG always frees up space inside the log file, even without NO_LOG, but doesn't make the log files any smaller physically. The space will get re-used for logging future transactions. You can physically shrink the log files to reclaim the space with SHRINK FILE.

For more important/active databases you can make backups more frequently: FULL - every day, DIFF - every 4 hours, LOGS - every 30 min.

3. Restore Sequence

1. Restore the most recent Full Database backup (created on 2008-12-30 00:00:00).

-- Assume the database is lost at this point.
-- This will restore all data in the backup and, if the backup contains log, rolls the database forward.
-- REPLACE - Overwrite the existing target database.
-- NORECOVERY - Allow subsequent restore operations to proceed.
RESTORE DATABASE databaseName
    FROM DISK = 'C:\BACKUP\SQL\DatabaseName_20081230_000000_full.bak'
    WITH FILE = 1, STATS = 10, REPLACE, NORECOVERY

2. Restore the last Differential Database backup (created on 2008-12-31 00:00:00).

-- Restore the differential database backup.
RESTORE DATABASE databaseName
    FROM DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_diff.dif'
    WITH FILE = 1, STATS = 10, NORECOVERY

3. Restore Transaction Log backup (only if databse Recovery Model is set to 'Full' or 'Bulk-Logged').

Restore each transaction log backup created after the differential database backup in same order as they were created

-- Restore transaction log backup 1 (created on 2008-12-31 01:00:00).
RESTORE DATABASE databaseName
    FROM DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 1, NORECOVERY
-- Restore transaction log backup 2 (append on 2008-12-31 02:00:00).
RESTORE DATABASE databaseName
    FROM DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 2, NORECOVERY
-- Restore transaction log backup 3 (created on 2008-12-31 03:00:00).
-- Use RECOVERY option to bring the database into a usable state and bring it online.
RESTORE DATABASE databaseName
    FROM DISK = 'C:\BACKUP\SQL\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 3, RECOVERY

Notes:

SQL Server will assume you’re not maintaining a sequence of log backups if any of the following are true:

You have truncated the log using BACKUP LOG WITH NO_LOG or BACKUP LOG WITH TRUNCATE_ONLY.

You have set the database to truncate the log automatically on a regular basis with the database option trunc. log on chkpt. or by setting the recovery mode to SIMPLE.

You have never taken a full database backup.

4. Restore As New Database

If you want to restore as new database (including Full Text Index) then you need to use MOVE option. You need to use prefix 'sysft_' fof Full Text Index when restoring.

USE MASTER

-- Restore Database
DECLARE @backupFile VarChar(100)
DECLARE @oldMdfName VarChar(50)
DECLARE @oldLdfName VarChar(50)
DECLARE @newMdfName VarChar(50)
DECLARE @newLdfName VarChar(50)
DECLARE @newTextDir VarChar(100)
DECLARE @newMdfFile VarChar(100)
DECLARE @newLdfFile VarChar(100)
 
SET @backupFile = 'D:\SQLBACK\DatabaseName_20081230_000000_full.bak'
SET @oldMdfName = 'DatabaseName'
SET @oldLdfName = 'DatabaseName_log'
SET @newMdfName = 'NewDatabaseName'
SET @newLdfName = 'NewDatabaseName_log'
SET @newTextDir = 'D:\SQLDATA_DB1\FTData'
SET @newMdfFile = 'D:\SQLDATA_DB1\NewDatabaseName.mdf' 
SET @newLdfFile = 'D:\SQLLOGS_DB1\NewDatabaseName.ldf'

RESTORE FILELISTONLY FROM DISK = @backupFile

-- Restore full database backup as new database.
RESTORE DATABASE @newMdfName
FROM DISK = @backupFile
WITH
    MOVE @oldMdfName TO @newMdfFile,
    MOVE @oldLdfName TO @newLdfFile,
    MOVE 'sysft_FullText1' TO 'D:\SQLDATA_DB1\FTData\FullText1',
    MOVE 'sysft_FullText2' TO 'D:\SQLDATA_DB1\FTData\FullText2',
    REPLACE, NORECOVERY
    
-- Restore the differential database backup.
RESTORE DATABASE newMdfName
    FROM DISK = 'C:\SQLBACK\DatabaseName_20081231_000000_diff.dif'
    WITH STATS = 10, NORECOVERY
    
-- Restore transaction log backup 1 (created on 2008-12-31 01:00:00).
RESTORE DATABASE newMdfName
    FROM DISK = 'C:\SQLBACK\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 1, NORECOVERY
-- Restore transaction log backup 2 (append on 2008-12-31 02:00:00).
RESTORE DATABASE newMdfName
    FROM DISK = 'C:\SQLBACK\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 2, NORECOVERY
-- Restore transaction log backup 3 (created on 2008-12-31 03:00:00).
-- Use RECOVERY option to bring the database into a usable state and bring it online.
RESTORE DATABASE newMdfName
    FROM DISK = 'C:\SQLBACK\DatabaseName_20081231_000000_logs.trn'
    WITH FILE = 3, RECOVERY

5. Logs

When you are backing up your Transaction Logs more frequently e.g. every 15 or 10 or 5 mins it is recommended to turn ON Trace flag 3226 so that the message "log was successfully backed up" will not be written to the SQL Server Logs and to the Windows Application Event Logs.

DBCC TRACEON (3226, -1) -- Enable global trace flag
DBCC TRACEOFF(3226, -1) -- Disable global trace flag
DBCC TRACESTATUS        -- List enabled trace flags